# -*- coding: utf-8 -*-
"""
数据库操作封装
"""

import os
from typing import List, Optional, Dict, Any
from datetime import datetime, timedelta
from sqlalchemy import create_engine, func, and_, or_
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.pool import StaticPool
import logging

from .models import Base, AlertRecord, MetadataSnapshot, MonitorMetrics, RuleConfig, SystemStatus

logger = logging.getLogger(__name__)


class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self, db_path: str = "data/monitor.db"):
        """
        初始化数据库管理器
        
        Args:
            db_path: SQLite数据库文件路径
        """
        # 确保数据目录存在
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        
        # 创建数据库引擎
        self.engine = create_engine(
            f'sqlite:///{db_path}',
            connect_args={'check_same_thread': False},
            poolclass=StaticPool,
            echo=False
        )
        
        # 创建会话工厂
        self.SessionLocal = sessionmaker(bind=self.engine)
        
        # 创建所有表
        Base.metadata.create_all(self.engine)
        
        logger.info(f"数据库初始化完成: {db_path}")
    
    def get_session(self) -> Session:
        """获取数据库会话"""
        return self.SessionLocal()
    
    # ==================== 告警记录操作 ====================
    
    def save_alert(self, alert_type: str, alert_level: str, database_name: str,
                   table_name: str = None, operation_user: str = None,
                   details: Dict = None, alert_message: str = None) -> AlertRecord:
        """保存告警记录"""
        session = self.get_session()
        try:
            alert = AlertRecord(
                alert_type=alert_type,
                alert_level=alert_level,
                database_name=database_name,
                table_name=table_name,
                operation_user=operation_user,
                operation_time=datetime.now(),
                details=details or {},
                alert_message=alert_message,
                alert_sent=True,
                alert_time=datetime.now()
            )
            session.add(alert)
            session.commit()
            session.refresh(alert)
            logger.info(f"保存告警记录: {alert.id}")
            return alert
        except Exception as e:
            session.rollback()
            logger.error(f"保存告警记录失败: {e}")
            raise
        finally:
            session.close()
    
    def get_alerts(self, limit: int = 100, offset: int = 0,
                   alert_level: str = None, database_name: str = None,
                   start_time: datetime = None, end_time: datetime = None,
                   resolved: bool = None) -> List[AlertRecord]:
        """查询告警记录"""
        session = self.get_session()
        try:
            query = session.query(AlertRecord)
            
            # 添加过滤条件
            if alert_level:
                query = query.filter(AlertRecord.alert_level == alert_level)
            
            if database_name:
                query = query.filter(AlertRecord.database_name == database_name)
            
            if start_time:
                query = query.filter(AlertRecord.alert_time >= start_time)
            
            if end_time:
                query = query.filter(AlertRecord.alert_time <= end_time)
            
            if resolved is not None:
                query = query.filter(AlertRecord.resolved == resolved)
            
            # 排序和分页
            query = query.order_by(AlertRecord.alert_time.desc())
            query = query.limit(limit).offset(offset)
            
            return query.all()
        finally:
            session.close()
    
    def get_alert_count(self, alert_level: str = None, database_name: str = None,
                       start_time: datetime = None, end_time: datetime = None) -> int:
        """获取告警数量"""
        session = self.get_session()
        try:
            query = session.query(func.count(AlertRecord.id))
            
            if alert_level:
                query = query.filter(AlertRecord.alert_level == alert_level)
            
            if database_name:
                query = query.filter(AlertRecord.database_name == database_name)
            
            if start_time:
                query = query.filter(AlertRecord.alert_time >= start_time)
            
            if end_time:
                query = query.filter(AlertRecord.alert_time <= end_time)
            
            return query.scalar()
        finally:
            session.close()
    
    def resolve_alert(self, alert_id: int, resolved_by: str = "system") -> bool:
        """标记告警为已解决"""
        session = self.get_session()
        try:
            alert = session.query(AlertRecord).filter(AlertRecord.id == alert_id).first()
            if alert:
                alert.resolved = True
                alert.resolved_time = datetime.now()
                alert.resolved_by = resolved_by
                session.commit()
                return True
            return False
        except Exception as e:
            session.rollback()
            logger.error(f"标记告警失败: {e}")
            return False
        finally:
            session.close()
    
    def get_alert_statistics(self, days: int = 7) -> Dict[str, Any]:
        """获取告警统计信息"""
        session = self.get_session()
        try:
            start_time = datetime.now() - timedelta(days=days)
            
            # 总告警数
            total_count = session.query(func.count(AlertRecord.id))\
                .filter(AlertRecord.alert_time >= start_time).scalar()
            
            # 按级别统计
            level_stats = session.query(
                AlertRecord.alert_level,
                func.count(AlertRecord.id)
            ).filter(AlertRecord.alert_time >= start_time)\
             .group_by(AlertRecord.alert_level).all()
            
            # 按类型统计
            type_stats = session.query(
                AlertRecord.alert_type,
                func.count(AlertRecord.id)
            ).filter(AlertRecord.alert_time >= start_time)\
             .group_by(AlertRecord.alert_type).all()
            
            # 未解决的告警数
            unresolved_count = session.query(func.count(AlertRecord.id))\
                .filter(and_(
                    AlertRecord.alert_time >= start_time,
                    AlertRecord.resolved == False
                )).scalar()
            
            return {
                'total_count': total_count,
                'level_stats': {level: count for level, count in level_stats},
                'type_stats': {type_: count for type_, count in type_stats},
                'unresolved_count': unresolved_count,
                'days': days
            }
        finally:
            session.close()
    
    # ==================== 元数据快照操作 ====================
    
    def save_snapshot(self, database_name: str, table_name: str,
                     partition_count: int = 0, row_count: int = 0,
                     total_size: int = 0, **kwargs) -> MetadataSnapshot:
        """保存元数据快照"""
        session = self.get_session()
        try:
            snapshot = MetadataSnapshot(
                database_name=database_name,
                table_name=table_name,
                partition_count=partition_count,
                row_count=row_count,
                total_size=total_size,
                **kwargs
            )
            session.add(snapshot)
            session.commit()
            session.refresh(snapshot)
            return snapshot
        except Exception as e:
            session.rollback()
            logger.error(f"保存快照失败: {e}")
            raise
        finally:
            session.close()
    
    def get_latest_snapshot(self, database_name: str, table_name: str) -> Optional[MetadataSnapshot]:
        """获取最新快照"""
        session = self.get_session()
        try:
            return session.query(MetadataSnapshot)\
                .filter(and_(
                    MetadataSnapshot.database_name == database_name,
                    MetadataSnapshot.table_name == table_name
                ))\
                .order_by(MetadataSnapshot.snapshot_time.desc())\
                .first()
        finally:
            session.close()
    
    def cleanup_old_snapshots(self, days: int = 30) -> int:
        """清理旧快照"""
        session = self.get_session()
        try:
            cutoff_time = datetime.now() - timedelta(days=days)
            count = session.query(MetadataSnapshot)\
                .filter(MetadataSnapshot.snapshot_time < cutoff_time)\
                .delete()
            session.commit()
            logger.info(f"清理了 {count} 条旧快照记录")
            return count
        except Exception as e:
            session.rollback()
            logger.error(f"清理旧快照失败: {e}")
            return 0
        finally:
            session.close()
    
    # ==================== 监控指标操作 ====================
    
    def save_metric(self, metric_name: str, metric_value: float,
                   metric_unit: str = None, tags: Dict = None) -> MonitorMetrics:
        """保存监控指标"""
        session = self.get_session()
        try:
            metric = MonitorMetrics(
                metric_name=metric_name,
                metric_value=metric_value,
                metric_unit=metric_unit,
                tags=tags or {}
            )
            session.add(metric)
            session.commit()
            session.refresh(metric)
            return metric
        except Exception as e:
            session.rollback()
            logger.error(f"保存指标失败: {e}")
            raise
        finally:
            session.close()
    
    # ==================== 规则配置操作 ====================
    
    def get_all_rules(self, enabled_only: bool = True) -> List[RuleConfig]:
        """获取所有规则"""
        session = self.get_session()
        try:
            query = session.query(RuleConfig)
            if enabled_only:
                query = query.filter(RuleConfig.enabled == True)
            return query.order_by(RuleConfig.priority.desc()).all()
        finally:
            session.close()
    
    def save_rule(self, rule_name: str, rule_type: str, conditions: Dict,
                 actions: Dict, **kwargs) -> RuleConfig:
        """保存规则配置"""
        session = self.get_session()
        try:
            rule = RuleConfig(
                rule_name=rule_name,
                rule_type=rule_type,
                conditions=conditions,
                actions=actions,
                **kwargs
            )
            session.add(rule)
            session.commit()
            session.refresh(rule)
            return rule
        except Exception as e:
            session.rollback()
            logger.error(f"保存规则失败: {e}")
            raise
        finally:
            session.close()
    
    # ==================== 系统状态操作 ====================
    
    def update_system_status(self, component: str, status: str,
                           message: str = None, metrics: Dict = None):
        """更新系统状态"""
        session = self.get_session()
        try:
            status_record = SystemStatus(
                component=component,
                status=status,
                message=message,
                metrics=metrics or {}
            )
            session.add(status_record)
            session.commit()
        except Exception as e:
            session.rollback()
            logger.error(f"更新系统状态失败: {e}")
        finally:
            session.close()
    
    def get_latest_status(self) -> Dict[str, Any]:
        """获取最新系统状态"""
        session = self.get_session()
        try:
            # 获取每个组件的最新状态
            components = ['monitor_engine', 'binlog_monitor', 'api_server', 'rule_engine']
            status_dict = {}
            
            for component in components:
                latest = session.query(SystemStatus)\
                    .filter(SystemStatus.component == component)\
                    .order_by(SystemStatus.status_time.desc())\
                    .first()
                
                if latest:
                    status_dict[component] = latest.to_dict()
            
            return status_dict
        finally:
            session.close()

